<?php

require CAMINHO_SERVIDOR.'conexao.php';
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of classificacaoDAO
 *
 * @author Fernando
 */
class classificacaoDAO {

    private $con;

    function __construct() {
        self::setCon(conectar());
        mysql_query('SET CHARACTER SET utf8');
    }

    public function getCon() {
        return $this->con;
    }

    public function setCon($con) {
        $this->con = $con;
    }

    public function fecharConexao() {
        desconectar(self::getCon());
    }

    function executaQuery($query) {
        return mysql_query($query, self::getCon());
    }

    function executaQueryAll($query) {
        $posts = array();
        $result = mysql_query($query, self::getCon());

        if (mysql_num_rows($result)) {
            while ($post = mysql_fetch_assoc($result)) {
                $posts[] = $post;
            }
        }

        return $posts;
    }

    function executaQueryRow($query) {
        $result = mysql_query($query, self::getCon());
        return mysql_fetch_assoc($result);
    }

    function getListaClassificacao($id_campeonato, $id_usuario, $paginar = false, $inicio = 0, $registros = 10) {
        $query = "SELECT usu.id_usuario,usu.nome,usu.id_user_facebook,

(SELECT if(SUM(p.pontos) is null,0,SUM(p.pontos)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as pts,

(SELECT if(SUM(p.ac) is null,0,SUM(p.ac)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as ac,

(SELECT if(SUM(p.acg) is null,0,SUM(p.acg)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acg,

(SELECT if(SUM(p.acr) is null,0,SUM(p.acr)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acr

FROM tb_amigos am INNER JOIN tb_usuario usu ON usu.id_usuario=am.id_usuario_amigo WHERE  am.id_usuario=" . $id_usuario . "

   UNION ALL
SELECT usu.id_usuario,usu.nome,usu.id_user_facebook,

(SELECT if(SUM(p.pontos) is null,0,SUM(p.pontos)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as pts,

(SELECT if(SUM(p.ac) is null,0,SUM(p.ac)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as ac,

(SELECT if(SUM(p.acg) is null,0,SUM(p.acg)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acg,

(SELECT if(SUM(p.acr) is null,0,SUM(p.acr)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acr


 FROM tb_amigos am INNER JOIN tb_usuario usu ON usu.id_usuario=am.id_usuario WHERE  am.id_usuario_amigo=" . $id_usuario . "


UNION ALL
SELECT usu.id_usuario,usu.nome,usu.id_user_facebook,

(SELECT if(SUM(p.pontos) is null,0,SUM(p.pontos)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as pts,

(SELECT if(SUM(p.ac) is null,0,SUM(p.ac)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as ac,

(SELECT if(SUM(p.acg) is null,0,SUM(p.acg)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acg,

(SELECT if(SUM(p.acr) is null,0,SUM(p.acr)) FROM tb_pontuacao p
INNER JOIN tb_usuario usua ON usua.id_usuario=p.id_usuario
INNER JOIN tb_apostas a ON a.id_aposta=p.id_aposta
INNER JOIN tb_jogos j ON j.id_jogo=a.id_jogo
INNER JOIN tb_campeonato c ON c.id_campeonato=j.id_campeonato
WHERE usua.id_usuario=usu.id_usuario AND c.id_campeonato=" . $id_campeonato . ") as acr


 FROM tb_usuario usu WHERE  usu.id_usuario=" . $id_usuario . " ORDER BY pts DESC,ac DESC,acg DESC,acr DESC,nome ASC";
        if ($paginar) {
            $query.=" LIMIT " . $inicio . "," . $registros;
        }
        $result = self::executaQueryAll($query);
        return $result;
    }

    function getCountListaClassificacao($id_usuario) {
        $query = "SELECT SUM(id) as total FROM (SELECT COUNT(usu.id_usuario) as id FROM tb_amigos am INNER JOIN tb_usuario usu ON usu.id_usuario=am.id_usuario_amigo WHERE  am.id_usuario=" . $id_usuario . " 
            UNION ALL
SELECT COUNT(usu.id_usuario) as id FROM tb_amigos am INNER JOIN tb_usuario usu ON usu.id_usuario=am.id_usuario WHERE  am.id_usuario_amigo=" . $id_usuario . "
UNION ALL
SELECT COUNT(usu.id_usuario) as id FROM tb_usuario usu WHERE  usu.id_usuario=" . $id_usuario . ") soma";
        $result = self::executaQueryRow($query);
        return $result['total'];
    }

}
